--As of July 21, 2014 2:39:32 PM -0700, Shreesha is alleged to have said:
> @Albe Laurenz:
> 'cache' was a typo. I meant file system level backup as mentioned here -
> (http://www.postgresql.org/docs/9.3/static/backup-file.html).
> It would be really helpful if you can give steps for automating the copy
> of PostgreSQL database cluster.
>
>
> To give a clear picture of what I am currently trying to do, Please find
> below:
> The system is using MySQL database and currently, we are doing backup
> ourselves with the help of a module which does the following:
> 1) LOCK TABLES which will internally create a global read lock for all
> the tables.
> 2) FLUSH TABLES
> 3) Iterate through all the tables in each database and create a copy of
> those files in destination location
> 4) UNLOCK the tables.
> Repeat these steps for every database.
--As for the rest, it is mine.
I understand that you are thinking 'copy the actual database files', but
might I suggest looking at pg_dump? It does essentially the above - the
'directory' format would even output one file per table. It does not lock
the database while it's in progress - but it does use Postgres's standard
concurrency control. (Meaning that it essentially will output a snapshot
of the database in time: While writes, deletes, etc. will work while
pg_dump is being run, the backup will contain only the data at the point
the backup started.) If you need further data integrity you can even use
the `--serializable-deferrable` switch. (Though note in nearly all cases
it's overkill; read the docs.)
The output would be more portable and manipulateable as well: You could do
things like restore only one table, or even possibly restore to other
databases. (From SQL format.) It would also output a platform-independent
dump, while your procedure above might not work with even a minor
difference in compiler between the original and restore database. (I'm not
sure how sensitive Postgres is to that type of thing, but the bare files
are not defined to be usable by anything except the exact binary that wrote
them.)
Daniel T. Staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------
